CREATE OR REPLACE PACKAGE MemoryTestQA IS

--===============================================================
-- Type declarations
--===============================================================

--===============================================================
-- Function and Procedure declarations
--===============================================================
FUNCTION ExecuteData (ContainerName IN Container.ContainerName%TYPE)
RETURN VARCHAR2
;
--===========================================================================
-- End of package specification
--===========================================================================
END;
/
CREATE OR REPLACE PACKAGE BODY MemoryTestQA AS

--=====================
-- ExecuteData
--=====================
FUNCTION ExecuteData (ContainerName IN Container.ContainerName%TYPE)
RETURN VARCHAR2 IS

    --=====================
    -- Input parameters variables
    --=====================
    vContainerName Container.ContainerName%TYPE := ContainerName;

    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Retrieve the Lot information
    --=====================

    --=====================
    -- Filter Lot, R type Lot need not do MTQA
    -- Deng Chao
    --=====================
    CURSOR curLot IS
    SELECT
        A.ContainerId
        , B.Device
        , B.QADDevice
        , C.SpecId
        , D.MfgOrderId
    FROM
        Container A
        , Product B
        , CurrentStatus C
        , MfgOrder D
    WHERE
        A.ProductId = B.ProductId
        AND C.CurrentStatusId = A.CurrentStatusId
        AND D.MfgOrderId (+) = A.MfgOrderId
        AND A.ContainerName = vContainerName
        AND A.LOTTYPE <> 'R'
    ;

    --=====================
    -- Retrieve the Lot's Wafer Run Number information
    --=====================
    CURSOR curWaferRunNumber IS
    SELECT
         B.WaferRunNumber
    FROM
        Container A
        , A_LotWaferRunNumbers B
    WHERE
        B.ContainerId (+) = A.ContainerId
        AND B.WaferRunNumberCategory (+) = 'M'
        AND A.ContainerName = vContainerName
    ;

    --=====================
    -- Retrieve Wafer MTQA Data by Work Order
    --=====================
    CURSOR curWaferMTQADataByWO (IN_Device A_WaferMTQAData.Device%TYPE
                                , IN_QADDevice A_WaferMTQAData.QADDevice%TYPE
                                , IN_Spec A_WaferMTQAData.SpecId%TYPE
                                , IN_WorkOrder A_WaferMTQAData.WorkOrderID%TYPE) IS
    SELECT
        A.*
    FROM
        A_WaferMTQAData A
    WHERE
        A.SpecId = IN_Spec
        AND A.WorkOrderId = IN_WorkOrder
    ;

    --=====================
    -- Retrieve Wafer MTQA Data By Wafer Run Number
    --=====================
    CURSOR curWaferMTQADataByWRN (IN_Device A_WaferMTQAData.Device%TYPE
                                 , IN_QADDevice A_WaferMTQAData.QADDevice%TYPE
                                 , IN_Spec A_WaferMTQAData.SpecId%TYPE
                                 , IN_WaferRunNumber A_WaferMTQAData.WaferRunNumber%TYPE) IS
    SELECT
        A.*
    FROM
        A_WaferMTQAData A
    WHERE
        A.SpecId = IN_Spec
        AND A.WaferRunNumber = IN_WaferRunNumber
    ;

    --=====================
    -- Retrieve the Lot Memory Test QA
    --=====================
    CURSOR curLotMTQA (IN_ContainerName Container.ContainerName%TYPE) IS
    SELECT
        NVL(B.QAResult, 'EMPTY') QAResult
    FROM
        Container A
        , A_LotMemoryTestQA B
    WHERE
        B.ContainerId = A.ContainerId
        AND B.RequireQAResult = 1
        AND A.ContainerName = IN_ContainerName
    ;

    --=====================
    -- Retrieve remaining lot for Work Order
    --=====================
    CURSOR curRemainByWO (IN_Spec A_WaferMTQAData.SpecId%TYPE
                         , IN_WorkOrder A_WaferMTQAData.WorkOrderId%TYPE) IS
    SELECT
        NVL(COUNT(A.ContainerName), 0) LotCount
    FROM
        Container A
        , CurrentStatus B
        , Spec C
        , SpecBase D
        , Spec E
        , SpecBase F
    WHERE
        A.MfgOrderId = IN_WorkOrder
        AND A.Status = 1
        AND B.CurrentStatusId = A.CurrentStatusId
        AND C.SpecId = B.SpecId
        AND C.ObjectCategory = 'WIP'
        AND D.SpecBaseId = C.SpecBaseId
        AND E.SpecId = IN_Spec
        AND F.SpecBaseId = E.SpecBaseId
        AND D.SpecName <= F.SpecName
    ;

    --=====================
    -- Retrieve remaining lot for wafer run number
    --=====================
    CURSOR curRemainByWRN (IN_Spec A_WaferMTQAData.SpecId%TYPE
                          , IN_WaferRunNumber A_WaferMTQAData.WaferRunNumber%TYPE) IS
    SELECT
        NVL(COUNT(A.ContainerName), 0) LotCount
    FROM
        Container A
        , A_LotWaferRunNumbers B
        , CurrentStatus C
        , Spec D
        , SpecBase E
        , Spec F
        , SpecBase G
    WHERE
        B.WaferRunNumberCategory = 'M'
        AND B.WaferRunNumber = IN_WaferRunNumber
        AND A.ContainerId = B.ContainerId
        AND A.Status = 1
        AND C.CurrentStatusId = A.CurrentStatusId
        AND D.SpecId = C.SpecId
        AND D.ObjectCategory = 'WIP'
        AND E.SpecBaseId = D.SpecBaseId
        AND F.SpecId = IN_Spec
        AND G.SpecBaseId = F.SpecBaseId
        AND E.SpecName <= G.SpecName
    ;

    --=====================
    -- Local variables
    --=====================
    rLot curLot%ROWTYPE;
    rWaferRunNumber curWaferRunNumber%ROWTYPE;
    rWaferMTQADataByWO curWaferMTQADataByWO%ROWTYPE;
    rWaferMTQADataByWRN curWaferMTQADataByWRN%ROWTYPE;
    rLotMTQA curLotMTQA%ROWTYPE;
    rRemainByWO curRemainByWO%ROWTYPE;
    rRemainByWRN curRemainByWRN%ROWTYPE;
    nQAStatus A_WaferMTQAData.QAStatus%TYPE := -1;
    vQAContainerName Container.ContainerName%TYPE;
    vQAType A_WaferMTQAData.QAType%TYPE;
    vQAResult A_LotMemoryTestQA.QAResult%TYPE;
    vReturn VARCHAR2(100) := '0';
    nCount int := 0;
    nFound int := 0;

    --=====================
    -- Pragma Declarations
    --=====================
    PRAGMA AUTONOMOUS_TRANSACTION;
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);

BEGIN

    --=====================
    -- Get Lot Information
    --=====================
    OPEN curLot;
    FETCH curLot INTO rLot;

    IF curLot%FOUND THEN

        --=====================
        -- Find MTQA By Work Order
        --=====================
        IF rLot.MfgOrderId IS NOT NULL THEN

            OPEN curWaferMTQADataByWO (rLot.Device, rLot.QADDevice, rLot.SpecId, rLot.MfgOrderId);
            FETCH curWaferMTQADataByWO INTO rWaferMTQADataByWO;

            IF curWaferMTQADataByWO%FOUND THEN

                vQAContainerName := rWaferMTQADataByWO.ContainerName;
                vQAType := rWaferMTQADataByWO.QAType;
                nQAStatus := rWaferMTQADataByWO.QAStatus;
                nCount := 0;
                nFound := 1;

                --=====================
                -- Get number of lots with similar Work Order
                --=====================
                OPEN curRemainByWO (rLot.SpecId, rLot.MfgOrderId);
                FETCH curRemainByWO INTO rRemainByWO;

                IF curRemainByWO%FOUND THEN
                    nCount := rRemainByWO.LotCount;
                END IF;

                CLOSE curRemainByWO;

                --=====================
                -- Set the return value
                --=====================
                IF vQAType = 'ALL' THEN

                    IF nQAStatus = 2 THEN
                        vQAResult := 'PASS';

                        FOR rLotMTQA in curLotMTQA(vQAContainerName) LOOP
                            IF rLotMTQA.QAResult <> 'PASS' THEN
                                vQAResult := 'EMPTY';
                            END IF;
                        END LOOP;


                        IF vQAResult <> 'PASS' THEN
                            RAISE_APPLICATION_ERROR (-20005, 'MemoryTestQA_E1001 ' || vContainerName || ' ' || vQAContainerName);
                        ELSE
                            --=====================
                            -- If all required result is PASS, then delete the Wafer MTQA Data
                            --=====================
                            IF nCount = 1 THEN
                                DELETE
                                FROM
                                    A_WaferMTQAData
                                WHERE
                                    WaferMTQADataId = rWaferMTQADataByWO.WaferMTQADataId
                                ;
                                COMMIT;
                            END IF;
                        END IF;
                    ELSE
                        RAISE_APPLICATION_ERROR (-20005, 'MemoryTestQA_E1001 ' || vContainerName || ' ' || vQAContainerName);
                    END IF;

                ELSE
                    IF VQAContainerName = vContainerName THEN

                        IF nQAStatus < 1 THEN
                            RAISE_APPLICATION_ERROR (-20005, 'MemoryTestQA_E1001 ' || vContainerName || ' ' || vQAContainerName);
                        ELSE
                            vQAResult := 'PASS';

                            FOR rLotMTQA in curLotMTQA(vQAContainerName) LOOP
                                IF rLotMTQA.QAResult <> 'PASS' THEN
                                    vQAResult := 'EMPTY';
                                END IF;
                            END LOOP;


                            IF vQAResult <> 'PASS' THEN
                                RAISE_APPLICATION_ERROR (-20005, 'MemoryTestQA_E1001 ' || vContainerName || ' ' || vQAContainerName);
                            ELSE
                                --=====================
                                -- If all required result is PASS, then delete the Wafer MTQA Data
                                --=====================
                                IF nCount = 1 THEN
		                    DELETE
		                    FROM
		                        A_WaferMTQAData
		                    WHERE
		                        WaferMTQADataId = rWaferMTQADataByWO.WaferMTQADataId
		                    ;
		                    COMMIT;
                                END IF;
                            END IF;
                        END IF;
                    END IF;
                END IF;
            END IF;

            CLOSE curWaferMTQADataByWO;

        END IF;

        --=====================
        -- Find MTQA By Wafer Run Number
        --=====================
        IF nFound = 0 THEN

            FOR rWaferRunNumber in curWaferRunNumber LOOP

                --=====================
                -- If Wafer MTQA Data found, check if QA Completed
                --=====================
                OPEN curWaferMTQADataByWRN (rLot.Device, rLot.QADDevice, rLot.SpecId, rWaferRunNumber.WaferRunNumber);
                FETCH curWaferMTQADataByWRN INTO rWaferMTQADataByWRN;

                IF curWaferMTQADataByWRN%FOUND THEN

                    vQAContainerName := rWaferMTQADataByWRN.ContainerName;
                    vQAType := rWaferMTQADataByWRN.QAType;
                    nQAStatus := rWaferMTQADataByWRN.QAStatus;
                    nCount := 0;

                    --=====================
                    -- Get number of lots with similar Wafer Run Number
                    --=====================
                    OPEN curRemainByWRN (rLot.SpecId, rWaferRunNumber.WaferRunNumber);
                    FETCH curRemainByWRN INTO rRemainByWRN;

                    IF curRemainByWRN%FOUND THEN
                        nCount := rRemainByWRN.LotCount;
                    END IF;

                    CLOSE curRemainByWRN;

                    --=====================
                    -- Set the return value
                    --=====================
                    IF vQAType = 'ALL' THEN

                        IF nQAStatus = 2 THEN
                            vQAResult := 'PASS';

                            FOR rLotMTQA in curLotMTQA(vQAContainerName) LOOP
                                IF rLotMTQA.QAResult <> 'PASS' THEN
                                    vQAResult := 'EMPTY';
                                END IF;
                            END LOOP;

                            IF vQAResult <> 'PASS' THEN
                                RAISE_APPLICATION_ERROR (-20005, 'MemoryTestQA_E1001 ' || vContainerName || ' ' || vQAContainerName);
                            ELSE
                                --=====================
                                -- If all required result is PASS, then delete the Wafer MTQA Data
                                --=====================
                                IF nCount = 1 THEN
 		                    DELETE
		                    FROM
		                        A_WaferMTQAData
		                    WHERE
		                        WaferMTQADataId = rWaferMTQADataByWRN.WaferMTQADataId
		                    ;
		                    COMMIT;
		                END IF;
                            END IF;
                        ELSE
                            RAISE_APPLICATION_ERROR (-20005, 'MemoryTestQA_E1001 ' || vContainerName || ' ' || vQAContainerName);
                        END IF;

                    ELSE

                        IF VQAContainerName = vContainerName THEN

                            IF nQAStatus < 1 THEN
                                RAISE_APPLICATION_ERROR (-20005, 'MemoryTestQA_E1001 ' || vContainerName || ' ' || vQAContainerName);
                            ELSE
                                vQAResult := 'PASS';

                                FOR rLotMTQA in curLotMTQA(vQAContainerName) LOOP
                                    IF rLotMTQA.QAResult <> 'PASS' THEN
                                        vQAResult := 'EMPTY';
                                    END IF;
                                END LOOP;

                                IF vQAResult <> 'PASS' THEN
                                    RAISE_APPLICATION_ERROR (-20005, 'MemoryTestQA_E1001 ' || vContainerName || ' ' || vQAContainerName);
                                ELSE
                                    --=====================
                                    -- If all required result is PASS, then delete the Wafer MTQA Data
                                    --=====================
                                    IF nCount = 1 THEN
		                       DELETE
		                        FROM
		                            A_WaferMTQAData
		                        WHERE
		                            WaferMTQADataId = rWaferMTQADataByWRN.WaferMTQADataId
		                        ;
		                        COMMIT;
                                    END IF;
                                END IF;
                            END IF;
                        END IF;
                    END IF;
                END IF;

                CLOSE curWaferMTQADataByWRN;

            END LOOP;

        END IF;

    END IF;

    CLOSE curLot;

    --=====================
    -- DELETE RECORD FOR R TYPE LOT.
    -- Deng Chao
    --=====================
    SELECT COUNT(*)
      INTO nCount
      FROM A_WAFERMTQADATA MTQA, CONTAINER CON
     WHERE MTQA.CONTAINERNAME = vContainerName
       AND MTQA.CONTAINERID = CON.CONTAINERID
       AND CON.LOTTYPE = 'R';
    
    IF nCOUNT <> 0 THEN
      DELETE FROM A_WAFERMTQADATA MTQA
       WHERE MTQA.CONTAINERNAME = vContainerName;
      COMMIT;
    END IF;
    
    --=====================
    -- Return success
    --=====================
    RETURN ('0');
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RETURN ('MemoryTestQA.ExecuteData->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        ROLLBACK;
        RETURN ('MemoryTestQA.ExecuteData->' || SQLERRM);
END ExecuteData;

--===========================================================================
-- End of package body
--===========================================================================
END;
/
